-- Add is_active and sort_order to all utility masters
-- Schema aligned with Notion Datapoints: https://www.notion.so/Datapoints-and-relationships-30c65b16e6898051b1edf9e6218f4f61

ALTER TABLE industries ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE industries ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE opportunity_stages ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;

ALTER TABLE user_roles ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE user_roles ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE seminar_modes ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE seminar_modes ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE corporate_statuses ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE corporate_statuses ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE designations ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE designations ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE lead_sources ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE lead_sources ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE locations ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE locations ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE followup_statuses ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE followup_statuses ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

ALTER TABLE product_interests ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE product_interests ADD COLUMN IF NOT EXISTS sort_order INT DEFAULT 0;

-- Validation constraints
ALTER TABLE corporates ADD CONSTRAINT chk_corporates_employee_base CHECK (employee_base IS NULL OR employee_base >= 0);
ALTER TABLE corporates ADD CONSTRAINT chk_corporates_number_of_locations CHECK (number_of_locations IS NULL OR number_of_locations >= 0);
ALTER TABLE branches ADD CONSTRAINT chk_branches_employee_strength CHECK (employee_strength IS NULL OR employee_strength >= 0);
ALTER TABLE opportunities ADD CONSTRAINT chk_opportunities_opportunity_cost CHECK (opportunity_cost IS NULL OR opportunity_cost >= 0);
ALTER TABLE opportunities ADD CONSTRAINT chk_opportunities_followup_count CHECK (followup_count >= 0);
ALTER TABLE seminars ADD CONSTRAINT chk_seminars_committed_attendees CHECK (committed_attendees IS NULL OR committed_attendees >= 0);
ALTER TABLE seminars ADD CONSTRAINT chk_seminars_actual_attendees CHECK (actual_attendees IS NULL OR actual_attendees >= 0);
